package DAO;

import Entity.News;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hieu Tran
 */
public class DAO_News {

    Connection conn = null;
    Statement state = null;
    ResultSet rs = null;

    public DAO_News() {
        //connection1("jdbc:odbc:StudentManagementDatabase");
        connection4("jdbc:sqlserver://localhost"
                + ":1433;databaseName=StudentManagementDatabase", "sa", "123456");
    }

    public final void connection1(String url) {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            try {
                conn = DriverManager.getConnection(url);
            } catch (SQLException ex) {
                Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println("DAO category is connected");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public final void connection4(String url, String username, String password) {
        try {
            //create driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            //get connection
            conn = DriverManager.getConnection(url,
                    username, password);
            System.out.println("DAO category is connected");

        } catch (ClassNotFoundException | SQLException ex) {
        }
    }

    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Author: HieuTD
     *
     * @param sql
     * @return
     */
    public ResultSet getData(String sql) {
        ResultSet dataset = null;
        try {
            state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            dataset = state.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }

        return dataset;

    }

    /**
     * Author: HieuTD
     *
     * @param article
     * @return number of affected rows
     */
    public int addNews(News article) {
        int n = 0;
        // prepare SQL statement
        String preSQL = "INSERT INTO News (title, slug, dateline, content, tags, cid, mid) VALUES (?,?,?,?,?,?,?)";

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            pre.setString(1, article.getTitle());
            pre.setString(2, article.getSlug());
            pre.setInt(3, article.getDateline());
            pre.setString(4, article.getContent());
            pre.setString(5, article.getTags());
            pre.setInt(6, article.getCid());
            pre.setInt(7, article.getMid());
            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        return n;
    }

    /**
     * Author: HieuTD
     *
     * @return
     */
    public List<News> newsList() {
        List<News> newsList = new ArrayList<>();
        try {
            String sql = "SELECT n.id, n.title, n.content, n.tags, c.name, m.username, n.dateline, n.slug FROM News AS n INNER JOIN Manager AS m ON n.mid = m.id INNER JOIN Category AS c ON c.id = n.cid ORDER BY n.id DESC";
            ResultSet list = getData(sql);
            while (list.next()) {
                int id = list.getInt(1);
                String title = list.getString(2);
                String content = list.getString(3);
                String tags = list.getString(4);                
                String catname = list.getString(5);
                String author = list.getString(6);
                int dateline = list.getInt(7);
                String slug = list.getString(8);
                News temp = new News(id, title, content, tags, slug, dateline, catname, author);
                newsList.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newsList;
    }

    /**
     * Author: HieuTD
     *
     * @param cid
     * @return
     */
    public List<News> newsInCat(int cid) {
        List<News> newsList = new ArrayList<>();
        try {
            String sql = "SELECT n.id, n.title, n.content, n.tags, c.name, m.username, n.dateline, n.slug FROM News AS n INNER JOIN Manager AS m ON n.mid = m.id INNER JOIN Category AS c ON c.id = n.cid WHERE c.id="+cid;
            ResultSet list = getData(sql);
            while (list.next()) {
                int id = list.getInt(1);
                String title = list.getString(2);
                String content = list.getString(3);
                String tags = list.getString(4);
                String catname = list.getString(5);
                String author = list.getString(6);
                int dateline = list.getInt(7);
                String slug = list.getString(8);
                News temp = new News(id, title, content, tags, slug, dateline, catname, author);
                newsList.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newsList;
    }

    /**
     * Author: HieuTD
     *
     * @param inSlug
     * @return
     */
    public List<News> newsInCat(String inSlug) {
        List<News> newsList = new ArrayList<>();
        try {
            String sql = "SELECT n.id, n.title, n.content, n.tags, c.name, m.username, n.dateline, n.slug FROM News AS n INNER JOIN Manager AS m ON n.mid = m.id INNER JOIN Category AS c ON c.id = n.cid WHERE c.slug='"+inSlug +"'";
            ResultSet list = getData(sql);
            while (list.next()) {
                int id = list.getInt(1);
                String title = list.getString(2);
                String content = list.getString(3);
                String tags = list.getString(4);
                String catname = list.getString(5);
                String author = list.getString(6);
                int dateline = list.getInt(7);
                String slug = list.getString(8);
                News temp = new News(id, title, content, tags, slug, dateline, catname, author);
                newsList.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newsList;
    }

    /**
     * Author: HieuTD
     *
     * @param inSlug
     * @return
     */
    public News getArticle(String inSlug) {
        News temp = null;
        try {
            String sql = "SELECT TOP(1) n.id, n.title, n.content, n.tags, c.name, m.username, n.dateline, n.slug FROM News AS n INNER JOIN Manager AS m ON n.mid = m.id INNER JOIN Category AS c ON c.id = n.cid WHERE n.slug=N'"+inSlug +"'";
            ResultSet list = getData(sql);
            while (list.next()) {
                int id = list.getInt(1);
                String title = list.getString(2);
                String content = list.getString(3);
                String tags = list.getString(4);
                String catname = list.getString(5);
                String author = list.getString(6);
                int dateline = list.getInt(7);
                String slug = list.getString(8);
                temp = new News(id, title, content, tags, slug, dateline, catname, author);
                
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp;
    }    
    /**
     * Author: HieuTD
     *
     * @param term
     * @return
     */
    public List<News> searchNews(String term) {
        List<News> newsList = new ArrayList<>();
        try {
            String sql = "SELECT n.id, n.title, n.content, n.tags, c.name, m.username, n.dateline, n.slug FROM News AS n INNER JOIN Manager AS m ON n.mid = m.id INNER JOIN Category AS c ON c.id = n.cid WHERE n.title LIKE N'%" + term + "%'";
            System.out.println(sql);
            ResultSet list = getData(sql);
            while (list.next()) {
                int id = list.getInt(1);
                String title = list.getString(2);
                String content = list.getString(3);
                String tags = list.getString(4);
                String catname = list.getString(5);
                String author = list.getString(6);
                int dateline = list.getInt(7);
                String slug = list.getString(8);
                News temp = new News(id, title, content, tags, slug, dateline, catname, author);
                newsList.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_News.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newsList;
    }      
    /**
     * Task: TramNN
     *
     * @param id
     * @return
     */
    public int deleteNews(int id) {
        int n = 0;
        /*
         TODO: Write the code here
         1. Write SQL Statement
         2. Execute
         */
        String preSQL = "DELETE FROM News WHERE id = " + id;

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        
        return n;
    }   
    
    /**
     * Task for: DongLD
     * @param news
     * @return 
     */
    public int updateNews(News news) {
        int n = 0;
        String preSQL = " UPDATE News ";
        preSQL+= " SET title = ?, slug = ?, dateline = ?, content = ?, tags = ?, cid = ?, mid = ? ";
        preSQL+= " WHERE id = ? ";
        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            pre.setString(1, news.getTitle());
            pre.setString(2, news.getSlug());
            pre.setInt(3, news.getDateline());
            pre.setString(4, news.getContent());
            pre.setString(5, news.getTags());
            pre.setInt(6, news.getCid());
            pre.setInt(7, news.getMid());
            pre.setInt(8, news.getId());
            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        return n;
    }
    
    /**
     * Just for test connection. Shift+F6 to run me!
     *
     * @param args
     */
    public static void main(String[] args) {
        DAO_News s = new DAO_News();
    }
}
